/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package register.ucm;

import common.ReConvertor;
import java.io.*;
import java.sql.*;
import register.model.InputValueModel;
import register.view.RegisterPanel;
import register.model.ButtonModel;


/**
 *
 * @author Vitamin
 */

public class RegisterMysqlInsert {

    final String url = "jdbc:mysql://192.168.222.101/isc?user=root&password=root&useUnicode=true";
    private Connection Register = null;
    private PreparedStatement RegRollStatement = null;
    private PreparedStatement RegStudentStatement = null;
    private PreparedStatement RegTeacherStatement = null;
    private PreparedStatement RegStaffStatement = null;
    private PreparedStatement RegRfidStatement = null;
    private PreparedStatement RegPrivilegeStatement = null;
    private PreparedStatement RegAccountStatement = null;
    private File file = null;
    private InputStream inputimage = null;
    private InputValueModel value;
    private ButtonModel button;
    private GetPathFromPhoto filepath;
    private String path;
    private String name;
    private String gender;
    private String address;
    private String phone;
    private String group;
    private String regDate;
    private String expDate;
    private String college;
    private String dormitory;
    private String title;
    private String degree;
    private String department;
    private String majorCourse;
    private String id;
    private String cid;
    private String psword;
    private String cpsword;
    private boolean stBook;
    private boolean stCourse;
    private boolean tBook;
    private boolean stfBook;
    private boolean stfAO;
    private boolean stfCA;
    private boolean stfLib;
    private boolean stfpio;
    private boolean stfsaa;
    private boolean stfsao;
    private String sqlurl;
    private static double init=0.0;

    public RegisterMysqlInsert()
    {
        value=RegisterPanel.getInstance().getInputValueModel();
        button=RegisterPanel.getInstance().getButtonModel();
        this.path=button.getPath().getText();
        this.id=value.getId();
        this.name=value.getName();
        this.gender=value.getGender();
        this.address=value.getAddress();
        this.phone=value.getPhone();
        this.group=value.getGroup();
        this.regDate=value.getRegDate();
        this.expDate=value.getExpDate();
        this.majorCourse=value.getMajorCourse();
        this.cid=value.getCardId();
        this.psword=value.getPassWord();
        this.cpsword=value.getConfirmPassWord();
        this.dormitory=value.getDormitory();
        this.college=value.getCollege();
        this.degree=value.getDegree();
        this.title=value.getTitle();
        this.department=value.getDepartment();

        this.stBook=value.getStBook();
        this.stCourse=value.getStCourse();
        this.tBook=value.getTBook();
        this.stfBook=value.getStfBook();
        this.stfAO=value.getStfAO();
        this.stfCA=value.getStfCA();
        this.stfLib=value.getStfLib();
        this.stfpio=value.getStfPIO();
        this.stfsaa=value.getStfSAA();
        this.stfsao=value.getStfSAO();
    }

    public boolean insertValue() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Register = DriverManager.getConnection(url);
            Register.setCatalog("isc");
            RegRollStatement = Register.prepareCall("INSERT INTO roll (id,card_id, name, groups, gender, reg_date, exp_date, dormitory, address, tele, photo)" +
                    " VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            RegRollStatement.setString(1, id);
            RegRollStatement.setString(2, cid);
            RegRollStatement.setString(3, name);
            RegRollStatement.setString(4, String.valueOf(ReConvertor.getInstance().rollGroup(group)));
            RegRollStatement.setString(5, gender);
            RegRollStatement.setString(6, regDate);
            RegRollStatement.setString(7, expDate);
            RegRollStatement.setString(8, dormitory);
            RegRollStatement.setString(9, address);
            RegRollStatement.setString(10, phone);
            file = new File(path);
            try {
                inputimage = new FileInputStream(file);
            } catch (Exception e) {
                System.out.println(e);
            }
            byte[] buf=new byte[(int)file.length()];
            inputimage.read(buf);
            RegRollStatement.setBytes(11, buf);
            RegRollStatement.executeUpdate();
            System.out.println("ROLL commit successfully");
            inputimage.close();
            RegRollStatement.close();


            sqlurl="UPDATE rfid SET roll_id=?, psw=? WHERE cid=?";
            RegRfidStatement=Register.prepareCall(sqlurl);
            RegRfidStatement.setString(1, id);
            RegRfidStatement.setString(2, psword);
            RegRfidStatement.setString(3, cid);
            RegRfidStatement.executeUpdate();
            System.out.println("RFID commit successfully");
            RegRfidStatement.close();

            sqlurl="INSERT INTO accountcenter (card_id, roll_id, name, account) VALUES (?,?,?,?)";
            RegAccountStatement=Register.prepareCall(sqlurl);
            RegAccountStatement.setString(1, cid);
            RegAccountStatement.setString(2, id);
            RegAccountStatement.setString(3, name);
            RegAccountStatement.setDouble(4, init);
            System.out.println("AccountCenter successfully");
            RegAccountStatement.close();
            
            if(group.equals("Student"))
            {
                System.out.println("Student part");
            sqlurl="INSERT INTO student (student_id, student_card, name, groups, college) VALUES (?,?,?,?,?)";
            RegStudentStatement=Register.prepareCall(sqlurl);
            RegStudentStatement.setString(1, id);
            RegStudentStatement.setString(2, cid);
            RegStudentStatement.setString(3, name);
            RegStudentStatement.setString(4, String.valueOf(ReConvertor.getInstance().studentGroup(degree)));
            RegStudentStatement.setString(5, String.valueOf(ReConvertor.getInstance().college(college)));
            RegStudentStatement.executeUpdate();
            System.out.println("STUDENT commit successfully");
            RegStudentStatement.close();
            }
            else if(group.equals("Teacher"))
            {
                System.out.println("Teacher part");
            sqlurl="INSERT INTO teacher (teacher_id, teacher_card, name, groups, college) VALUES(?,?,?,?,?)";
            RegTeacherStatement=Register.prepareCall(sqlurl);
            RegTeacherStatement.setString(1, id);
            RegTeacherStatement.setString(2, cid);
            RegTeacherStatement.setString(3, name);
            RegTeacherStatement.setString(4, String.valueOf(ReConvertor.getInstance().teacherGroup(title)));
            RegTeacherStatement.setString(5, String.valueOf(ReConvertor.getInstance().college(college)));
            RegTeacherStatement.executeUpdate();
            System.out.println("TEACHER commit successfully");
            RegTeacherStatement.close();
            }
            else if(group.equals("Staff"))
            {
                System.out.println("Staff part");
            sqlurl="INSERT INTO staff (staff_id, staff_card, name, groups) VALUES (?,?,?,?)";
            RegStaffStatement=Register.prepareCall(sqlurl);
            RegStaffStatement.setString(1, id);
            RegStaffStatement.setString(2, cid);
            RegStaffStatement.setString(3, name);
            RegStaffStatement.setString(4, String.valueOf(ReConvertor.getInstance().staffGroup(department)));
            RegStaffStatement.executeUpdate();
            System.out.println("STAFF commit successfully");
            RegStaffStatement.close();
            }

            sqlurl="INSERT INTO privilege(roll_id, card_id, account_operator, roll_operator, achieve_operator, " +
                    "assoc_admin, course_operator, choose_course, library_admin, borrow_book)" +
                    "VALUES (?,?,?,?,?,?,?,?,?,?)";
            RegPrivilegeStatement=Register.prepareCall(sqlurl);
            RegPrivilegeStatement.setString(1, id);
            RegPrivilegeStatement.setString(2, cid);
            this.convertParam();
            RegPrivilegeStatement.executeUpdate();
            System.out.println("PRIVILEGE commit successfully");
            RegPrivilegeStatement.close();
            Register.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public void convertParam()
    {
        try{

        if(this.stfAO==true)
        {
            RegPrivilegeStatement.setString(3, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(3, "N");
        }

        if(this.stfpio==true)
        {
            RegPrivilegeStatement.setString(4, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(4, "N");
        }

        if(this.stfsao==true)
        {
            RegPrivilegeStatement.setString(5, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(5, "N");
        }

        if(this.stfsaa==true)
        {
            RegPrivilegeStatement.setString(6, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(6, "N");
        }

        if(this.stfCA==true)
        {
            RegPrivilegeStatement.setString(7, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(7, "N");
        }

        if(this.stCourse==true)
        {
            RegPrivilegeStatement.setString(8, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(8, "N");
        }

        if(this.stfLib==true)
        {
            RegPrivilegeStatement.setString(9, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(9, "N");
        }

        if(this.stBook==true || this.tBook==true || this.stfBook==true)
        {
            RegPrivilegeStatement.setString(10, "Y");
        }
        else
        {
            RegPrivilegeStatement.setString(10, "N");
        }

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

    }
}
